﻿using System;
using System.Data;
using Dapper;
using System.Linq;
using SilenthinkContract;
using System.Collections.Generic;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    public class PaymentService : ServiceBase, IPaymentService
    {
        public UserAccount GetUserAccount(long id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Payment.Table.UserAccount)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return connection.Query<UserAccount>(template).FirstOrDefault();
            }
        }

        //public PageList<UserRecharge> GetPagedUserRecharge(long? userId = null, byte? status = null, int index = 1,int size = 20)
        //{
        //    var template = SqlTemplate.PassportTemplate.GetPagedUserRecharge(userId, status, index, size);
        //    using (var connection = GetConnection(ConnectionName.Passport))
        //    {
        //        return PageList<UserRecharge>(connection, template);
        //    }
        //}

        #region UserTrade
        public bool CreateUserTrade(UserTrade o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Payment.Table.UserTrade)
            .Field("([Id],[UserId],[Title],[Category],[FundFlow],[Amount],[Paid],[Refunded],[ForUserId],[ForUserName],[AssistNo],[Extra],[Status],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@Title,@Category,@FundFlow,@Amount,@Paid,@Refunded,@ForUserId,@ForUserName,@AssistNo,@Extra,@Status,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.Id, o.UserId, o.Title, o.Category, o.FundFlow, o.Amount, o.Paid, o.Refunded, o.ForUserId, o.ForUserName, o.AssistNo, o.Extra, o.Status, o.CreatedBy, o.CreatedOn });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }
        public UserTrade GetUserTrade(string id, long? userId = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Payment.Table.UserTrade)
                .Where("[Id] = @Id", new { id });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return connection.Query<UserTrade>(template).FirstOrDefault();
            }
        }

        public UserTrade GetUserTrade(string id, byte[] version)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Payment.Table.UserTrade)
                .Where("[Id] = @Id", new { id })
                .Where("[Version] = @Version", new { version });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return connection.Query<UserTrade>(template).FirstOrDefault();
            }

        }

        public bool UpdateUserTrade(UserTrade o)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region UserIntegral

        public bool CreateUserIntegral(UserIntegral o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.UserIntegral)
                .Field(
                    "([UserId],[Category],[Point],[CorrelationId],[Expiration],[Status],[Memo],[CreatedBy],[CreatedOn])VALUES(@UserId,@Category,@Point,@CorrelationId,@Expiration,@Status,@Memo,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new {
                        o.UserId,
                        o.Category,
                        o.Point,
                        o.CorrelationId,
                        o.Expiration,
                        o.Status,
                        o.Memo,
                        o.CreatedBy,
                        o.CreatedOn
                    });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateUserIntegral(UserIntegral o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.UserIntegral)
                .Field("[UserId]=@UserId", new { o.UserId })
                .Field("[Category]=@Category", new { o.Category })
                .Field("[Point]=@Point", new { o.Point })
                .Field("[CorrelationId]=@CorrelationId", new { o.CorrelationId })
                .Field("[Expiration]=@Expiration", new { o.Expiration })
                .Field("[Status]=@Status", new { o.Status })
                .Field("[Memo]=@Memo", new { o.Memo })
                .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteUserIntegral(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.UserIntegral)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public UserIntegral GetUserIntegral(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Payment.Table.UserIntegral)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserIntegral>(connection, template).FirstOrDefault();
            }
        }

        public PageList<UserIntegral> GetPagedUserIntegral(long? userId = null, IntegralCategory? category = null,
                                                                 IntegralStatus? status = null, int index = 1,
                                                                 int size = 20)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Payment.Table.UserIntegral)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            /*
            .Where("");
            */

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return PageList<UserIntegral>(connection, template);
            }
        }

        #endregion

        #region PaymentBank

        public bool CreatePaymentBank(PaymentBank o)
        {
            const string field =
                @"([Name],[Image],[Description],[Status],[CreatedBy],[CreatedOn])
                VALUES(@Name,@Image,@Description,@Status,@CreatedBy,@CreatedOn)";
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentBank)
                .Field(field)
                .AddParameters(new { o.Name, o.Image, o.Description, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdatePaymentBank(PaymentBank o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentBank)
                .Field("[Name]=@Name", new { o.Name })
                .Field("[Image]=@Image", new { o.Image })
                .Field("[Description]=@Description", new { o.Description })
                .Field("[Status]=@Status", new { o.Status })
                .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeletePaymentBank(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentBank)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public PaymentBank GetPaymentBank(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Payment.Table.PaymentBank)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Query<PaymentBank>(connection, template).FirstOrDefault();
            }
        }

        public IList<PaymentBank> GetAllPaymentBank()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Payment.Table.PaymentBank);

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Query<PaymentBank>(connection, template).ToList();
            }
        }

        public IDictionary<int, string> GetAvailablePaymentBank(int platformId)
        {
            const string tpl =
                "SELECT [Id],[Name] FROM {0} WHERE  [Status]=1 AND Id NOT IN(SELECT BankId FROM {1} WHERE [PaymentPlatformId]=@PlatformId)";
            var sql = string.Format(tpl, Database.Payment.Table.PaymentBank,
                                    Database.Payment.Table.PaymentEBank);

            var template = new SqlBuilder().AddParameters(new { platformId }).AddTemplate(sql);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Query<dynamic>(connection, template).ToDictionary(o => (int)o.Id, o => (string)o.Name);
            }
        }

        public PageList<PaymentBank> GetPagedPaymentBank(int index = 1, int size = 20, string name = null,
                                                         bool? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Payment.Table.PaymentBank)
                .OrderBy("[Status] DESC")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            if (!String.IsNullOrEmpty(name))
            {
                builder.Where("[Name] LIKE @Name", new { name = name + "%" });
            }
            if (status.HasValue)
            {
                builder.Where("[Status] = @Status", new { status });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return PageList<PaymentBank>(connection, template);
            }
        }

        #endregion

        #region PaymentEBank

        public bool CreatePaymentEBank(PaymentEBank o)
        {
            const string field =
                @"([BankId],[PlatformId],[GatewayCode],[Description],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])
            VALUES(@BankId,@PlatformId,@GatewayCode,@Description,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)";

            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentEBank)
                .Field(field)
                .AddParameters(
                    new {
                        o.BankId,
                        o.PlatformId,
                        o.GatewayCode,
                        o.Description,
                        DisplayOrder = o.Priority,
                        o.Status,
                        o.CreatedBy,
                        o.CreatedOn
                    });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdatePaymentEBank(PaymentEBank o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentEBank)
                .Field("[BankId]=@BankId", new { o.BankId })
                .Field("[PlatformId]=@PlatformId", new { o.PlatformId })
                .Field("[GatewayCode]=@GatewayCode", new { o.GatewayCode })
                .Field("[Description]=@Description", new { o.Description })
                .Field("[Priority]=@Priority", new { o.Priority })
                .Field("[Status]=@Status", new { o.Status })
                .Where("[Id] = @Id", new { o.Id });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeletePaymentEBank(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentEBank)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public PaymentEBank GetPaymentEBank(int id, bool widthDetail = false)
        {
            SqlBuilder.Template template;
            if (widthDetail)
            {
                const string tpl = "SELECT * FROM /**table**/ /**innerjoin**/ /**where**/";
                var builder = new SqlBuilder()
                    .Table(Database.Payment.Table.PaymentEBank + " a")
                    .InnerJoin(Database.Payment.Table.PaymentBank + " b on a.BankId=b.Id")
                    .InnerJoin(Database.Payment.Table.PaymentPlatform + "  c on a.PaymentPlatformId=c.Id");
                if (id > 0)
                {
                    builder.Where("a.Id=@Id", new { id });
                }
                template = builder.AddTemplate(tpl);
            }
            else
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(Database.Payment.Table.PaymentEBank)
                    .Where("[Id] = @Id", new { id });
                template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            }

            using (var connection = GetConnection(ConnectionName.Payment))
            {
                if (widthDetail)
                {
                    connection.EnsureOpen();
                    return
                        connection.Query<PaymentEBank, PaymentBank, PaymentPlatform, PaymentEBank>(template.RawSql,
                                                                                                   (a, b, c) => {
                                                                                                       a.PaymentBank = b;
                                                                                                       a.PaymentPlatform = c;
                                                                                                       return a;
                                                                                                   },
                                                                                                   template.Parameters).
                            FirstOrDefault();
                }
                return Query<PaymentEBank>(connection, template).FirstOrDefault();
            }
        }

        public IList<PaymentEBank> GetAllPaymentEBank(int? platformId = null, bool? status = null, string name = null)
        {
            const string tpl = "SELECT * FROM /**table**/ /**innerjoin**/ /**where**/";
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentEBank + " a")
                .InnerJoin(Database.Payment.Table.PaymentBank + " b on a.BankId=b.Id")
                .InnerJoin(Database.Payment.Table.PaymentPlatform + "  c on a.PlatformId=c.Id");
            if (platformId.HasValue && platformId.Value > 0)
            {
                builder.Where("a.PlatformId=@PlatformId", new { platformId });
            }
            if (status.HasValue)
            {
                builder.Where("a.Status = @Status", new { status });
            }
            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("b.Name LIKE @Name", new { name = name + "%" });
            }
            var template = builder.AddTemplate(tpl);
            using (var connection = GetConnection(ConnectionName.Payment).EnsureOpen())
            {
                return
                    connection.Query<PaymentEBank, PaymentBank, PaymentPlatform, PaymentEBank>(template.RawSql,
                                                                                               (a, b, c) => {
                                                                                                   a.PaymentBank = b;
                                                                                                   a.PaymentPlatform = c;
                                                                                                   return a;
                                                                                               },
                                                                                               template.Parameters)
                        .ToList();
            }
        }

        #endregion

        #region PaymentPlatform

        public bool CreatePaymentPlatform(PaymentPlatform o)
        {
            const string field = @"([Name],[Image],[MerchantId],[PrivateKey],[PublicKey],[GatewayUrl],[CallbackUrl],[NotifyUrl],[QueryUrl],[RefoundUrl],[Description],[Priority],[Extra],[Status],[CreatedBy],[CreatedOn])
            VALUES(@Name,@Image,@MerchantId,@PrivateKey,@PublicKey,@GatewayUrl,@CallbackUrl,@NotifyUrl,@QueryUrl,@RefoundUrl,@Description,@Priority,@Extra,@Status,@CreatedBy,@CreatedOn)";

            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentPlatform).Field(field)
               .AddParameters(new { o.Name, o.Image, o.MerchantId, o.PrivateKey, o.PublicKey, o.GatewayUrl, o.CallbackUrl, o.NotifyUrl, o.QueryUrl, o.RefoundUrl, o.Description, o.Priority, o.Extra, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdatePaymentPlatform(PaymentPlatform o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentPlatform)
                .Field("[Name]=@Name", new { o.Name })
                .Field("[Image]=@Image", new { o.Image })
                .Field("[MerchantId]=@MerchantId", new { o.MerchantId })
                .Field("[PrivateKey]=@PrivateKey", new { o.PrivateKey })
                .Field("[PublicKey]=@PublicKey", new { o.PublicKey })
                .Field("[GatewayUrl]=@GatewayUrl", new { o.GatewayUrl })
                .Field("[CallbackUrl]=@CallbackUrl", new { o.CallbackUrl })
                .Field("[NotifyUrl]=@NotifyUrl", new { o.NotifyUrl })
                .Field("[QueryUrl]=@QueryUrl", new { o.QueryUrl })
                .Field("[RefoundUrl]=@RefoundUrl", new { o.RefoundUrl })
                .Field("[Description]=@Description", new { o.Description })
                .Field("[Priority]=@Priority", new { o.Priority })
                .Field("[Extra]=@Extra", new { o.Extra })
                .Field("[Status]=@Status", new { o.Status })
                .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeletePaymentPlatform(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.PaymentPlatform)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Execute(connection, template) == 1;
            }
        }

        public PaymentPlatform GetPaymentPlatform(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Payment.Table.PaymentPlatform)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Query<PaymentPlatform>(connection, template).FirstOrDefault();
            }
        }

        public IList<PaymentPlatform> GetAllPaymentPlatform(bool? status = null, bool widthEbanks = false)
        {
            SqlBuilder.Template template;
            if (widthEbanks)
            {
                var a = "SELECT * FROM {0} WHERE 1=1" + (status.HasValue ? " AND [Status]=@Status;" : string.Empty);
                var b = "SELECT * FROM {1} WHERE 1=1" + (status.HasValue ? " AND [Status]=@Status;" : string.Empty);
                var tpl = string.Format(a + b, Database.Payment.Table.PaymentPlatform,
                                        Database.Payment.Table.PaymentEBank);
                var builder = new SqlBuilder();
                if (status.HasValue)
                {
                    builder.AddParameters(new { status });
                }
                template = builder.AddTemplate(tpl);
            }
            else
            {
                var builder = new SqlBuilder().Field("*")
                    .Table(Database.Payment.Table.PaymentPlatform);
                if (status.HasValue)
                {
                    builder.AddParameters(new { status });
                }
                template = builder.AddTemplate(SqlTemplate.Select);
            }

            if (widthEbanks)
            {
                using (var connection = GetConnection(ConnectionName.Payment).EnsureOpen())
                {
                    return connection.QueryMultiple(template).Map<PaymentPlatform, PaymentEBank, int>
                        (a => a.Id, b => b.PlatformId, (a, b) => { a.EBanks = b; }).ToList();
                }
            }
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Query<PaymentPlatform>(connection, template).ToList();
            }
        }

        #endregion

        #region UserRecharge

        public bool CreateUserRecharge(UserRecharge o, PayTrade trade)
        {
            var builder = new SqlBuilder()
            .Table(Database.Payment.Table.UserRecharge)
            .Field("([Id],[UserId],[Amount],[Paid],[Refunded],[Category],[PackageId],[Gateway],[Extra],[Status],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@Amount,@Paid,@Refunded,@Category,@PackageId,@Gateway,@Extra,@Status,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.Id, o.UserId, o.Amount, o.Paid, o.Refunded, o.Category, o.PackageId, o.Gateway, o.Extra, o.Status, o.CreatedBy, o.CreatedOn });
            var template = builder.AddTemplate(SqlTemplate.Insert);

            var tradeBuilder = new SqlBuilder()
            .Table(Database.Payment.Table.PayTrade)
            .Field("([Id],[UserId],[Category],[AssociatedId],[Amount],[Balance],[UseBalance],[PaymentCategory],[Gateway],[BankName],[PaymentEBankId],[PaymentPlatformId],[CardNo],[CardPwd],[CardAmount],[IpAddress],[ThirdPartyNo],[Extra],[Status],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@Category,@AssociatedId,@Amount,@Balance,@UseBalance,@PaymentCategory,@Gateway,@BankName,@PaymentEBankId,@PaymentPlatformId,@CardNo,@CardPwd,@CardAmount,@IpAddress,@ThirdPartyNo,@Extra,@Status,@CreatedBy,@CreatedOn)")
            .AddParameters(new { trade.Id, trade.UserId, trade.Category, trade.AssociatedId, trade.Amount, trade.Balance, trade.UseBalance, trade.PaymentCategory, trade.Gateway, trade.BankName, trade.PaymentEBankId, trade.PaymentPlatformId, trade.CardNo, trade.CardPwd, trade.CardAmount, trade.IpAddress, trade.ThirdPartyNo, trade.Extra, trade.Status, trade.CreatedBy, trade.CreatedOn });

            var tradeTemplate = tradeBuilder.AddTemplate(SqlTemplate.Insert);

            using (var connection = GetConnection(ConnectionName.Payment).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var staus = Execute(connection, template,transaction) == 1;
                    if (staus)
                    {
                        staus = Execute(connection, tradeTemplate, transaction) == 1;
                        if (staus)
                        {
                            transaction.Commit();
                            return true;
                        }
                        transaction.Rollback();
                        return false;
                    }
                    return false;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    return false;
                }
            }
        }

        public bool UpdateUserRecharge(UserRecharge o)
        {
            throw new NotImplementedException();
        }
        public int GetUserRechargeCount(long userId, byte? status=null)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.UserRecharge)
                .Where("[UserId]=@UserId", new {userId});
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new {status});
            }
            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Query<int>(connection, template).FirstOrDefault();
            }
        }

        public int GetUserUsedPackageCount(long userId,int packageId)
        {
            var builder = new SqlBuilder()
                .Table(Database.Payment.Table.UserRecharge)
                .Where("[Status]=0 AND [UserId]=@UserId AND [PackageId] = @PackageId", new { userId, packageId });

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return Query<int>(connection, template).FirstOrDefault();
            }
        }

        public bool DeleteUserRecharge(string id)
        {
            throw new NotImplementedException();
        }

        public UserRecharge GetUserRecharge(string id)
        {
            throw new NotImplementedException();
        }

        public IList<UserRecharge> GetAllUserRecharge()
        {
            throw new NotImplementedException();
        }

        public PageList<UserRecharge> GetPagedUserRecharge(long? userId, byte? status = null, string userName = null, int index = 1, int size = 20, DateTime? beginDate = null, DateTime? endDate = null)
        {
            var builder = new SqlBuilder().Field("*")
                           .Table(Database.Payment.Table.UserRecharge)
                           .OrderBy("[CreatedBy] desc")
                           .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            if (!string.IsNullOrEmpty(userName))
            {
                builder.Where("[CreatedBy]=@CreatedBy", new { CreatedBy = userName });
            }

            if (beginDate.HasValue)
            {
                var date = new DateTime(beginDate.Value.Year, beginDate.Value.Month, beginDate.Value.Day);
                builder.Where("[CreatedOn]>=@CreatedOn", new { CreatedOn = date });
            }

            if (endDate.HasValue)
            {
                var date = new DateTime(endDate.Value.Year, endDate.Value.Month, endDate.Value.Day);
                builder.Where("[CreatedOn]<@CreatedOn", new { CreatedOn = date.AddDays(1) });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return PageList<UserRecharge>(connection, template);
            }
        }

        #endregion

        #region  RechargePackage


        public IList<RechargePackage> GetAllRechargePackage(bool? status = null, string name = null)
        {
            var x = "SELECT * FROM {0} WHERE 1=1" + (status.HasValue ? " AND [Status]=@Status;" : string.Empty);
            var y = "SELECT * FROM {1} ";
            var tpl = string.Format(x + y, Database.Payment.Table.RechargePackage,
                                    Database.Payment.Table.RechargePackageRequirement);
            var builder = new SqlBuilder();
            if (status.HasValue)
            {
                builder.AddParameters(new {status});
            }
            var template = builder.AddTemplate(tpl);
            IList<RechargePackage> packages;
            IList<RechargePackageRequirement> requirements;
             using (var connection = GetConnection(ConnectionName.Payment).EnsureOpen())
            using (var reader = connection.QueryMultiple(template))
            {
                packages = reader.Read<RechargePackage>().ToList();
                requirements = reader.Read<RechargePackageRequirement>().ToList();
            }

            if (packages.Any())
            {
                foreach (var package in packages)
                {
                    var temp = package;
                    package.Requirements = requirements.Where(o => temp.PackageIds.Contains(o.Id.ToString())).ToList();
                }
            }
            return packages;
        }

        #endregion

        #region PayTrade

        //public bool CreatePayTrade(PayTrade o)
        //{
        //    var builder = new SqlBuilder()
        //    .Table(Database.Payment.Table.PayTrade)
        //    .Field("([Id],[UserId],[Category],[UserTradeId],[Amount],[Balance],[UseBalance],[PaymentCategory],[Gateway],[BankName],[PaymentEBankId],[PaymentPlatformId],[CardNo],[CardPwd],[CardAmount],[IpAddress],[ThirdPartyNo],[Extra],[Status],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@Category,@UserTradeId,@Amount,@Balance,@UseBalance,@PaymentCategory,@Gateway,@BankName,@PaymentEBankId,@PaymentPlatformId,@CardNo,@CardPwd,@CardAmount,@IpAddress,@ThirdPartyNo,@Extra,@Status,@CreatedBy,@CreatedOn)")
        //    .AddParameters(new { o.Id, o.UserId, o.Category, o.UserTradeId, o.Amount, o.Balance, o.UseBalance, o.PaymentCategory, o.Gateway, o.BankName, o.PaymentEBankId, o.PaymentPlatformId, o.CardNo, o.CardPwd, o.CardAmount, o.IpAddress, o.ThirdPartyNo, o.Extra, o.Status, o.CreatedBy, o.CreatedOn });

        //    var template = builder.AddTemplate(SqlTemplate.Insert);
        //    using (var connection = GetConnection(ConnectionName.Payment))
        //    {
        //        return Execute(connection, template) == 0;
        //    }
        //}
        public bool PayByBalance(PayTrade o)
        {
            var param = new DynamicParameters();
            param.AddDynamicParams(new {o.Id, o.UserId, username = o.CreatedBy, o.Amount, tradeId = o.AssociatedId, o.IpAddress});

            param.Add("status", 1, DbType.Int32, ParameterDirection.Output);
            param.Add("message", string.Empty, DbType.String, ParameterDirection.Output, 200);

            using (var connection = GetConnection(ConnectionName.Payment))
            {
                connection.Execute("[Proc.Pay.By.Balance]", param, null, 3, CommandType.StoredProcedure);
                return param.Get<int>("status") == 0;
            }
        }

        public bool UpdatePayTrade(PayTrade o)
        {
            throw new NotImplementedException();
        }

        public PayTrade GetPayTrade(string id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Payment.Table.PayTrade)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Payment))
            {
                return connection.Query<PayTrade>(template).FirstOrDefault();
            }
        }


        #endregion
        public bool BidCurrencyIncrease(int userId, string username, int amount, bool free, int category, string associatedId, string extra,out string message)
        {
            var param = new DynamicParameters();
            param.AddDynamicParams(new { userId, username,amount,free,category,associatedId,extra });

            param.Add("status", 1, DbType.Int32, ParameterDirection.Output);
            param.Add("message", string.Empty, DbType.String, ParameterDirection.Output, 200);

            using (var connection = GetConnection(ConnectionName.Payment))
            {
                connection.Execute("[Proc.Currency.Bid.Increase]", param, null, 3, CommandType.StoredProcedure);
                message = param.Get<string>("message");
                return param.Get<int>("status") == 0;
            }
        }

        #region Implementation of IService

        public int GetCachedSQLCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }

        public List<Tuple<string, string, int>> GetCachedSQL()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }

        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }

        #endregion
    }
}